package com.lagou.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;


import com.lagou.dao.ManagerDao;
import com.lagou.dao.UserDao;
import com.lagou.entity.Business;
import com.lagou.entity.Company;
import com.lagou.entity.First;
import com.lagou.entity.Position;
import com.lagou.entity.Second;
import com.lagou.entity.Third;
import com.lagou.entity.UBasic;
import com.lagou.entity.User;
import com.lagou.util.JDBCUtil2;

public class ManagerDaoImpl implements ManagerDao{
	JDBCUtil2 util=JDBCUtil2.getInit();
	public List<Company> queryByPage(int page,int size) {
		List<Company> list=new ArrayList<Company>();
		String sql="SELECT *  from company order by cid  limit "+(page-1)*size+","+size;
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				Company c=new Company();
				c.setCid(rs.getInt("cid"));
				c.setCname(rs.getString("cname"));
				c.setBid(rs.getInt("bid"));
				c.setCshortName(rs.getString("cshortname"));
				c.setClogo(rs.getString("clogo"));
				c.setCcid(rs.getString("ccid"));
				c.setFid(rs.getString("fid"));
				c.setCdevelopment(rs.getString("cdevelopment"));
				c.setCtag(rs.getString("ctag"));
				c.setCboss(rs.getString("cboss"));
				c.setClincense(rs.getString("clincense"));
				c.setCstatus(rs.getInt("cstatus"));			
				list.add(c);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public int getCount() {
		String sql="SELECT Count(cid) mycount from company";
		CachedRowSet rs=util.query(sql);
		try {
			if(rs.next()) {
				return rs.getInt("mycount");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return -1;
	}
	@Override
	public int getPositionCount() {
		String sql="SELECT Count(pid) mycount from position";
		CachedRowSet rs=util.query(sql);
		try {
			if(rs.next()) {
				return rs.getInt("mycount");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return -1;
	}
	@Override
	public List<Position> queryPositionByPage(int page, int size) {
		List<Position> list=new ArrayList<Position>();
		String sql="SELECT *  from position order by pid  limit "+(page-1)*size+","+size;
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				Position p=new Position();
				p.setPid(rs.getInt("pid"));
				p.setSid(rs.getInt("sid"));
				p.setTid(rs.getInt("tid"));
				p.setCid(rs.getInt("cid"));
				p.setPclass(rs.getString("pclass"));
				p.setPname(rs.getString("pname"));
				p.setPdep(rs.getString("pdep"));
				p.setPnature(rs.getString("pnature"));
				p.setMonthlyMin(rs.getInt("monthlyMin"));
				p.setMonthlyMax(rs.getInt("monthlyMax"));
				p.setPcity(rs.getString("pcity"));
				p.setTreatment(rs.getString("treatment"));
				p.setPaddress(rs.getString("paddress"));
				p.setReleaseTime(rs.getString("releaseTime"));
				p.setPstatus(rs.getInt("pstatus"));
				
						
				list.add(p);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public List<Company> queryAll() {
		List<Company> list=new ArrayList<Company>();
		CachedRowSet rs=util.query("SELECT  * from company ");
		try {
			while(rs.next()) {
				Company c=new Company();
				
				c.setCid(rs.getInt("cid"));
				c.setBid(rs.getInt("bid"));
				c.setCname(rs.getString("cname"));
				c.setClogo(rs.getString("clogo"));
				list.add(c);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public int getUBCount() {
		String sql="SELECT Count(ubid) mycount from t_ubasic";
		CachedRowSet rs=util.query(sql);
		try {
			if(rs.next()) {
				return rs.getInt("mycount");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return -1;
	}
	@Override
	public List<UBasic> queryUBByPage(int page, int size) {
		
		List<UBasic> list=new ArrayList<UBasic>();
		String sql="SELECT *  from t_ubasic order by ubid  limit "+(page-1)*size+","+size;
		CachedRowSet rs=util.query(sql);
		UserDao dao=new UserDaoImpl();
		try {
			while(rs.next()) {
				UBasic ub=new UBasic();
				ub.setUbid(rs.getInt("ubid"));
				User u=dao.queryById(rs.getInt("u_id"));
				ub.setUser(u);
				ub.setUname(rs.getString("u_name"));
				ub.setUgender(rs.getString("u_gender"));
				ub.setUeducation(rs.getString("u_education"));
				ub.setPhone(rs.getString("phone"));
				ub.setPhoto(rs.getString("photo"));
				ub.setUsn(rs.getString("usn"));
				ub.setUstatus(rs.getInt("u_status"));
						
				list.add(ub);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public List<Second> queryAllSecond() {
		List<Second> list=new ArrayList<Second>();
		String sql="select * from t_second";
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				Second s=new Second();
				s.setFid(rs.getInt("fid"));
				s.setSid(rs.getInt("sid"));
				s.setsName(rs.getString("sname"));
				
				list.add(s);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public List<Third> queryAllThird() {
		List<Third> list=new ArrayList<Third>();
		String sql="select * from t_third";
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				Third t=new Third();
				
				t.setSid(rs.getInt("sid"));
				t.setTid(rs.getInt("tid"));
				t.settName(rs.getString("tname"));
				
				
				
				
				list.add(t);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public boolean addf(First f) {
		boolean flag=util.update("insert into t_first (ftype) values(?)", f.getfType());
		if(flag) {
			return true;
		}else {
			return false;
		}
	}
	@Override
	public boolean adds(Second s) {
		boolean flag=util.update("insert into t_second (sname,fid) values(?,?)", s.getsName(),s.getFid());
		if(flag) {
			return true;
		}else {
			return false;
		}
	}
	@Override
	public boolean addt(Third t) {
		boolean flag=util.update("insert into t_third (tname,sid,fid) values(?,?,?)", t.gettName(),t.getSid(),t.getFid());
		if(flag) {
			return true;
		}else {
			return false;
		}
	}
	@Override
	public First queryByFName(String name) {
		
		
		String sql="SELECT * from t_first where ftype=?";
		CachedRowSet rs=util.query(sql,name);
		try {
			if(rs.next()) {
				First f=new First();
			f.setFid(rs.getInt("fid"));
			f.setfType(rs.getString("ftype"));
				
				return f;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;

}
	@Override
	public List<Second> querySecondByFId(int id) {
		List<Second> list=new ArrayList<Second>();
		
		String sql="SELECT *  from t_second  where fid=?";
		CachedRowSet rs=util.query(sql,id);
		try {
			while(rs.next()) {
				Second s=new Second();
				s.setFid(rs.getInt("fid"));
				s.setSid(rs.getInt("sid"));
				s.setsName(rs.getString("sname"));
				
				
				
				
				
				
						
				list.add(s);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public Second queryBySName(String name) {
		String sql="SELECT * from t_second where sname=?";
		CachedRowSet rs=util.query(sql,name);
		try {
			if(rs.next()) {
				
				Second s=new Second();
				s.setFid(rs.getInt("fid"));
				s.setSid(rs.getInt("sid"));
				s.setsName(rs.getString("sname"));
				
				return s;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}
	@Override
	public List<Third> querySecondByTId(int id) {
List<Third> list=new ArrayList<Third>();

		
		String sql="SELECT *  from t_third  where sid=?";
		CachedRowSet rs=util.query(sql,id);
		try {
			while(rs.next()) {
				Third t=new Third();
				
				t.setSid(rs.getInt("sid"));
				t.setTid(rs.getInt("tid"));
				t.settName(rs.getString("tname"));
					
				list.add(t);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public boolean updateByBId(int st,int bid) {
		
			boolean flag=util.update("UPDATE t_business SET b_status=? WHERE b_id=? ",st,bid);
			if(flag) {
				return true;
			}else {
				return false;
			
		}
	}
	@Override
	public boolean updateByPId(int pid, int st) {
		boolean flag=util.update("UPDATE position SET pstatus=? WHERE pid=? ",st,pid);
		if(flag) {
			return true;
		}else {
			return false;
		
	}
	}
	@Override
	public boolean updateByUId(int uid, int st) {
		boolean flag=util.update("UPDATE t_user SET u_status=? WHERE u_id=? ",st,uid);
		if(flag) {
			return true;
		}else {
			return false;
		
	}
	}
	@Override
	public List<Business> queryAllB() {
		
		List<Business> list=new ArrayList<Business>();
		String sql="select * from t_business";
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				Business b=new Business();
				b.setBid(rs.getInt("b_id"));
				b.setBstatus(rs.getInt("b_status"));
				
				list.add(b);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
	@Override
	public boolean updateByCId(int bid, int st) {
		boolean flag=util.update("UPDATE company SET Cstatus=? WHERE bid=? ",st,bid);
		if(flag) {
			return true;
		}else {
			return false;
		
	}
	}
	@Override
	public List<User> queryAllU() {
		
		List<User> list=new ArrayList<User>();
		String sql="select * from t_user";
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				User u=new User();
				u.setUid(rs.getInt("u_id"));
				u.setUstatus(rs.getInt("u_status"));
				
				
				list.add(u);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}
}

